#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库迁移：场馆定价模式更新为按人头收费
- 修改VenueTimeSlot表：price改为per_person_price（每人价格）
- 添加Order表字段：participant_count（参与人数）、actual_attendance（实际到场人数）
- 添加活动参与统计功能
"""

import mysql.connector
import os
from datetime import datetime
from dotenv import load_dotenv

# 加载环境变量
load_dotenv()

def get_db_connection():
    """获取数据库连接"""
    try:
        # 从环境变量读取数据库配置
        connection = mysql.connector.connect(
            host=os.getenv('MYSQL_HOST', 'localhost'),
            port=int(os.getenv('MYSQL_PORT', '3306')),
            user=os.getenv('MYSQL_USER', 'root'),
            password=os.getenv('MYSQL_PASSWORD', ''),
            database=os.getenv('MYSQL_DATABASE', 'venue_booking_system'),
            charset='utf8mb4'
        )
        return connection
    except mysql.connector.Error as e:
        print(f"数据库连接失败: {e}")
        return None

def migrate_venue_pricing_model():
    """执行场馆定价模式迁移"""
    connection = get_db_connection()
    if not connection:
        return False
    
    cursor = connection.cursor()
    
    try:
        print("🚀 开始执行场馆定价模式迁移...")
        
        # 1. 修改venue_time_slots表结构
        print("📝 更新venue_time_slots表结构...")
        
        # 添加per_person_price字段（如果不存在）
        cursor.execute("""
            SELECT COUNT(*) 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE table_name = 'venue_time_slots' 
            AND column_name = 'per_person_price'
            AND table_schema = DATABASE()
        """)
        
        if cursor.fetchone()[0] == 0:
            cursor.execute("""
                ALTER TABLE venue_time_slots 
                ADD COLUMN per_person_price DECIMAL(10,2) DEFAULT 0.00 
                COMMENT '每人价格' AFTER price
            """)
            print("  - 添加per_person_price字段")
        else:
            print("  - per_person_price字段已存在")
        
        # 将现有price值复制到per_person_price（作为每人的基础价格）
        cursor.execute("""
            UPDATE venue_time_slots 
            SET per_person_price = price 
            WHERE per_person_price = 0.00
        """)
        
        # 添加最小收费人数字段（如果不存在）
        cursor.execute("""
            SELECT COUNT(*) 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE table_name = 'venue_time_slots' 
            AND column_name = 'min_charge_count'
            AND table_schema = DATABASE()
        """)
        
        if cursor.fetchone()[0] == 0:
            cursor.execute("""
                ALTER TABLE venue_time_slots 
                ADD COLUMN min_charge_count INTEGER DEFAULT 1 
                COMMENT '最小收费人数' AFTER per_person_price
            """)
            print("  - 添加min_charge_count字段")
        else:
            print("  - min_charge_count字段已存在")
        
        # 2. 修改orders表结构
        print("📝 更新orders表结构...")
        
        # 添加参与人数字段（如果不存在）
        cursor.execute("""
            SELECT COUNT(*) 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE table_name = 'orders' 
            AND column_name = 'participant_count'
            AND table_schema = DATABASE()
        """)
        
        if cursor.fetchone()[0] == 0:
            cursor.execute("""
                ALTER TABLE orders 
                ADD COLUMN participant_count INTEGER DEFAULT 1 
                COMMENT '预约参与人数' AFTER amount
            """)
            print("  - 添加participant_count字段")
        else:
            print("  - participant_count字段已存在")
        
        # 添加实际到场人数字段（如果不存在）
        cursor.execute("""
            SELECT COUNT(*) 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE table_name = 'orders' 
            AND column_name = 'actual_attendance'
            AND table_schema = DATABASE()
        """)
        
        if cursor.fetchone()[0] == 0:
            cursor.execute("""
                ALTER TABLE orders 
                ADD COLUMN actual_attendance INTEGER DEFAULT 0 
                COMMENT '实际到场人数' AFTER participant_count
            """)
            print("  - 添加actual_attendance字段")
        else:
            print("  - actual_attendance字段已存在")
        
        # 添加签到状态字段（如果不存在）
        cursor.execute("""
            SELECT COUNT(*) 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE table_name = 'orders' 
            AND column_name = 'checkin_status'
            AND table_schema = DATABASE()
        """)
        
        if cursor.fetchone()[0] == 0:
            cursor.execute("""
                ALTER TABLE orders 
                ADD COLUMN checkin_status TINYINT DEFAULT 0 
                COMMENT '签到状态：0-未签到，1-已签到，2-迟到，3-缺席' AFTER actual_attendance
            """)
            print("  - 添加checkin_status字段")
        else:
            print("  - checkin_status字段已存在")
        
        # 3. 创建场馆时段预订统计视图
        print("📊 创建预订统计视图...")
        cursor.execute("""
            CREATE OR REPLACE VIEW venue_booking_statistics AS
            SELECT 
                v.id as venue_id,
                v.name as venue_name,
                vts.id as time_slot_id,
                vts.day_of_week,
                vts.start_time,
                vts.end_time,
                vts.capacity as max_capacity,
                vts.per_person_price,
                COALESCE(SUM(o.participant_count), 0) as total_booked,
                COALESCE(SUM(o.actual_attendance), 0) as total_attended,
                (vts.capacity - COALESCE(SUM(o.participant_count), 0)) as available_spots,
                COUNT(DISTINCT o.id) as booking_count,
                ROUND(COALESCE(SUM(o.participant_count), 0) / NULLIF(vts.capacity, 0) * 100, 2) as occupancy_rate
            FROM venues v
            LEFT JOIN venue_time_slots vts ON v.id = vts.venue_id
            LEFT JOIN orders o ON vts.id = o.time_slot_id 
                AND o.status IN (1, 3) -- 已支付或已完成的订单
                AND o.reservation_date >= CURDATE() - INTERVAL 30 DAY -- 最近30天的数据
            WHERE v.is_deleted = 0 AND vts.status = 1
            GROUP BY v.id, v.name, vts.id, vts.day_of_week, vts.start_time, vts.end_time, 
                     vts.capacity, vts.per_person_price
            ORDER BY v.id, vts.day_of_week, vts.start_time
        """)
        
        # 4. 创建实时预订状态表
        print("📋 创建实时预订状态表...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS venue_booking_status (
                id INT AUTO_INCREMENT PRIMARY KEY,
                venue_id INT NOT NULL,
                time_slot_id INT NOT NULL,
                booking_date DATE NOT NULL,
                booked_count INT DEFAULT 0 COMMENT '已预订人数',
                attended_count INT DEFAULT 0 COMMENT '实际到场人数',
                capacity INT NOT NULL COMMENT '容量',
                last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_venue_date (venue_id, booking_date),
                INDEX idx_timeslot_date (time_slot_id, booking_date),
                UNIQUE KEY uk_venue_timeslot_date (venue_id, time_slot_id, booking_date),
                FOREIGN KEY (venue_id) REFERENCES venues(id) ON DELETE CASCADE,
                FOREIGN KEY (time_slot_id) REFERENCES venue_time_slots(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='场馆预订状态实时统计表'
        """)
        
        # 5. 更新现有订单的participant_count（默认为1人）
        print("🔄 更新现有订单数据...")
        cursor.execute("""
            UPDATE orders 
            SET participant_count = 1 
            WHERE participant_count = 0 OR participant_count IS NULL
        """)
        
        # 6. 跳过触发器创建（需要SUPER权限）
        print("⚠️  跳过触发器创建（需要SUPER权限）...")
        
        # 7. 初始化现有数据的预订状态
        print("📊 初始化预订状态数据...")
        cursor.execute("""
            INSERT INTO venue_booking_status (venue_id, time_slot_id, booking_date, booked_count, attended_count, capacity)
            SELECT 
                o.venue_id,
                o.time_slot_id,
                o.reservation_date,
                SUM(o.participant_count) as booked_count,
                SUM(o.actual_attendance) as attended_count,
                MAX(vts.capacity) as capacity
            FROM orders o
            JOIN venue_time_slots vts ON o.time_slot_id = vts.id
            WHERE o.status IN (1, 3) -- 已支付或已完成
            GROUP BY o.venue_id, o.time_slot_id, o.reservation_date
            ON DUPLICATE KEY UPDATE
                booked_count = VALUES(booked_count),
                attended_count = VALUES(attended_count)
        """)
        
        connection.commit()
        print("✅ 场馆定价模式迁移完成！")
        print("\n📋 迁移总结:")
        print("- ✅ venue_time_slots表新增per_person_price（每人价格）字段")
        print("- ✅ venue_time_slots表新增min_charge_count（最小收费人数）字段") 
        print("- ✅ orders表新增participant_count（参与人数）字段")
        print("- ✅ orders表新增actual_attendance（实际到场人数）字段")
        print("- ✅ orders表新增checkin_status（签到状态）字段")
        print("- ✅ 创建venue_booking_statistics统计视图")
        print("- ✅ 创建venue_booking_status实时状态表")
        print("- ✅ 创建数据同步触发器")
        print("- ✅ 初始化现有数据")
        
        return True
        
    except mysql.connector.Error as e:
        print(f"❌ 迁移失败: {e}")
        connection.rollback()
        return False
    finally:
        cursor.close()
        connection.close()

if __name__ == "__main__":
    print("=" * 60)
    print("🏟️  场馆定价模式升级迁移工具")
    print("=" * 60)
    
    success = migrate_venue_pricing_model()
    if success:
        print("\n🎉 迁移成功完成！系统现在支持按人头收费模式。")
    else:
        print("\n�� 迁移失败，请检查错误信息并重试。") 